<?php
Class Report_model extends CI_Model {
public function __construct()
{
$this->load->database('hms');
}
	function goc()
	{
	$sql="
	SELECT r.room_id, r.room_number, rt.room_type_name
	FROM room as r, room_type as rt
	WHERE  r.room_type_id = rt.room_type_id
	AND r.deleted = 'No' and rt.deleted = 'No' 
	GROUP BY r.room_number";
	return $this->db->query($sql);
	}
	function daily_occ_report($date, $room_id)
	{
	$sql="
	SELECT  COUNT(rr.room_id) as room 
	FROM room as r, reservation_room as rr, room_type as rt
	WHERE r.room_id = rr.room_id AND r.room_type_id = rt.room_type_id AND rr.deleted = 'No'
	AND r.deleted = 'No' and rt.deleted = 'No' AND rr.resv_room_date ='".$date."' and
	rr.room_id = '".$room_id."'";
	return $this->db->query($sql);
	
	}
	function month_occ_report_get ($month, $year)
	{
	$sql="
	SELECT r.room_number, rt.room_type_name, COUNT(rr.room_id) as room , DAY(LAST_DAY(rr.resv_room_date)) as totaldate
	FROM room as r, reservation_room as rr, room_type as rt
	WHERE r.room_id = rr.room_id AND r.room_type_id = rt.room_type_id AND rr.deleted = 'No'
	AND r.deleted = 'No' and rt.deleted = 'No' AND MONTH(rr.resv_room_date) = ".$month." 
	AND YEAR(rr.resv_room_date)= ".$year."
	GROUP BY rr.room_id";
	return $this->db->query($sql);
	}
	
	
	function get_information_inv ($is)
	{
		$sql="
		SELECT s.serv_ord_id, s.room_name, DATE_FORMAT(s.serv_ord_time,'%d-%m-%Y %T') as tanggal, u.emp_name FROM service_order as s, user as u
		WHERE  s.user_id = u.user_id AND s.serv_ord_id ='".$is."'";
		return $this->db->query($sql);
	}
	function get_service ($ord)
	{
		$sql = "
		SELECT s.service_code, s.service_name, FORMAT(sd.serv_det_qty,0) as qty, sd.serv_det_price,(sd.serv_det_qty * sd.serv_det_price) as total
		FROM service as s, service_detail as sd, service_order as so 
		WHERE so.serv_ord_id = sd.serv_ord_id AND sd.service_id = s.service_id and so.serv_ord_id ='".$ord."'";
		return $this->db->query($sql);
	}
	function total_service ($ord)
	{
		$sql= "
		SELECT Sum(sd.serv_det_qty * sd.serv_det_price) as subtotal,
		so.discount_percent,
		so.service_tax,
		so.ppn_tax,
		so.discount_nominal,
		so.serv_ord_total,
		so.serv_ord_paid
		FROM service as s, service_detail as sd, service_order as so 
		WHERE so.serv_ord_id = sd.serv_ord_id AND sd.service_id = s.service_id and so.serv_ord_id ='".$ord."'";
		return $this->db->query($sql);
	}

   function service_report($data1,$data2)
    {
		$sql="SELECT resv_serv_date as Date,guest_name as Name,room_number as Room,service_type_name as Type,service_name as Service,service_price as Harga,resv_serv_unit as Jumlah, resv_serv_unit*service_price as Total
        FROM guest,reservation,service,reservation_service,service_type
        WHERE reservation_service.service_id=service.service_id 
        and reservation_service.resv_id=reservation.resv_id 
        and reservation.guest_id=guest.guest_id 
        and service_type.service_type_id=service.service_type_id
        and resv_booking_time between '".$data1."' and '".$data2."'";
        return $this->db->query($sql);         
    }
	
	function room_report($data1,$data2){
		$sql="SELECT distinct room.room_number as room, room_type_name as roomname, resv_checkout- resv_checkin as roomnights,(resv_checkout- resv_checkin)*room_type_price as roomprice
		FROM room, room_type, reservation 
		WHERE room.room_type_id=room_type.room_type_id 
		and reservation.room_number=room.room_number
		and resv_checkin between '".$data1."' and '".$data2."' 
		and resv_checkout between '".$data1."' and '".$data2."'";
        return $this->db->query($sql);         
	}
	
	function daily_report($data1,$data2){
		
		$sql="SELECT RES.RESV_CHECKIN AS DATE, RO.ROOM_NUMBER AS ROOM, RT.ROOM_TYPE_NAME AS 
		TYPE , GUE.GUEST_NAME AS NAME, RES.RESV_STATUS AS 
		STATUS , SE.SERVICE_NAME AS SERVICE, RRO.PRICE AS ROOM_PRICE, SE.SERVICE_PRICE
		FROM RESERVATION RES, ROOM RO, ROOM_TYPE RT, GUEST GUE, RESERVATION_ROOM RRO, RESERVATION_SERVICE RSE, SERVICE SE
		WHERE RES.GUEST_ID = GUE.GUEST_ID
		AND RES.ROOM_NUMBER = RO.ROOM_NUMBER
		AND RT.ROOM_TYPE_ID = RO.ROOM_TYPE_ID
		AND RRO.RESV_ID = RES.RESV_ID
		AND RSE.RESV_ID = RES.RESV_ID
		where resv_checkin >= '".$data1."' 
		and 
		resv_checkout < '".$data2."'
		";
        return $this->db->query($sql);     
	}
	
function dailyocc_report($data1,$data2){

$sql="SELECT distinct x.resv_checkin, x.resv_checkout, x.room_number, x.room_type_name, x.guest_name, x.resv_status, x.price AS total
FROM (

SELECT d.resv_checkin, d.resv_checkout, e.resv_room_date, b.room_number, a.room_type_name, c.guest_name, d.resv_status, sum( e.price ) AS price
FROM room_type a, room b, guest c, reservation d, reservation_room e
WHERE a.room_type_id = b.room_type_id
AND b.room_number = d.room_number
AND d.resv_id = e.resv_id
AND c.guest_id = d.guest_id
GROUP BY e.resv_room_date, b.room_number, a.room_type_name, c.guest_name, d.resv_status
)x, (

SELECT e.resv_room_date, d.room_number, sum( b.resv_serv_total ) AS Price
FROM reservation_service b, reservation c, room d, reservation_room e
WHERE c.resv_id = e.resv_id
AND b.resv_id = c.resv_id
AND c.room_number = d.room_number
GROUP BY e.resv_room_date, d.room_number
)y
WHERE x.resv_room_date = y.resv_room_date
AND x.room_number = y.room_number
AND x.resv_room_date between '".$data1."' and '".$data2."'";


        return $this->db->query($sql);         
     
}

	function daily_sales_report($data1,$data2){
		$sql="SELECT resv_serv_date as Date,
		guest_name as Name,
		room.room_number as Room,
		room_type_name as Type,
		service_name as Service,
		resv_checkin as checkin,
		resv_checkout as checkout,
		resv_checkout- resv_checkin as roomnights,
		(resv_checkout- resv_checkin)*room_type_price as roomprice,
		(resv_serv_unit*service_price)+((resv_checkout- resv_checkin)*room_type_price) as Total
 FROM guest,reservation,service,room, room_type,reservation_service,service_type
        WHERE reservation_service.service_id=service.service_id 
        and reservation_service.resv_id=reservation.resv_id 
        and reservation.guest_id=guest.guest_id 
        and service_type.service_type_id=service.service_type_id
        and resv_booking_time between '".$data1."' and '".$data2."'";
        return $this->db->query($sql); 
	}
	function reservation_report($data1,$data2){

		$sql="SELECT RES.RESV_CHECKIN AS CHECKIN, RES.RESV_CHECKOUT AS CHECKOUT, RO.ROOM_NUMBER AS ROOM, RT.ROOM_TYPE_NAME AS 
		TYPE , GUE.GUEST_NAME AS NAME, RES.RESV_STATUS AS STATUS 
		FROM reservation RES, room RO, room_type RT, guest GUE, reservation_room RRO
		WHERE RES.GUEST_ID = GUE.GUEST_ID
		AND RES.ROOM_NUMBER = RO.ROOM_NUMBER
		AND RT.ROOM_TYPE_ID = RO.ROOM_TYPE_ID
		AND RRO.RESV_ID = RES.RESV_ID
		AND resv_checkin >= '".$data1."' 
		AND 
		resv_checkout < '".$data2."'
		group by RES.RESV_ID
		";
        return $this->db->query($sql);
	}
	
	function select_daily_service($date) {
		$this->db->select('so.serv_ord_id, g.guest_name, g.nationality, so.room_name, DATE_FORMAT(so.serv_ord_time,\'%H:%i\') as serv_ord_time, st.service_type_name, s.service_code, s.service_name, sd.serv_det_price, sd.serv_det_qty, s.service_uom, so.discount_percent, so.discount_nominal, so.ppn_tax, so.service_tax', FALSE);
		//$this->db->select('*');
		$this->db->from('service_detail as sd');
		$this->db->join('service_order as so', 'so.serv_ord_id=sd.serv_ord_id', 'left');
		$this->db->join('reservation as r', 'r.resv_id=so.resv_id', 'left');
		$this->db->join('guest as g', 'g.guest_id=r.guest_id', 'left');
		$this->db->join('service as s', 's.service_id=sd.service_id', 'left');
		$this->db->join('service_type as st', 'st.service_type_id=s.service_type_id', 'left');
		$this->db->where('DATE(so.serv_ord_time)', $date);
		$this->db->where('sd.deleted', 'no');
		$this->db->where('s.deleted', 'no');
		$this->db->order_by('so.serv_ord_time', 'asc');
		
		return $this->db->get();
	}
	
	function select_monthly_service($month, $year) {
		$this->db->select('so.serv_ord_id, so.room_name, DATE(so.serv_ord_time) as serv_ord_time, st.service_type_name, s.service_code, s.service_name, sd.serv_det_price, sd.serv_det_qty, s.service_uom, so.discount_percent, so.discount_nominal, so.ppn_tax, so.service_tax', FALSE);
		//$this->db->select('*');
		$this->db->from('service_detail as sd');
		$this->db->join('service_order as so', 'so.serv_ord_id=sd.serv_ord_id', 'left');
		$this->db->join('service as s', 's.service_id=sd.service_id', 'left');
		$this->db->join('service_type as st', 'st.service_type_id=s.service_type_id', 'left');
		$this->db->where('MONTH(so.serv_ord_time)', $month);
		$this->db->where('YEAR(so.serv_ord_time)', $year);
		$this->db->where('sd.deleted', 'no');
		$this->db->where('s.deleted', 'no');
		$this->db->order_by('so.serv_ord_time', 'asc');
		
		return $this->db->get();
	}
	
	function select_daily_occupied($date) {
		$this->db->select('r.room_id, r.room_number, rt.room_type_name');
		$this->db->from('reservation_room as rr');
		$this->db->join('room as r', 'r.room_id=rr.room_id', 'left');
		$this->db->join('room_type as rt', 'rt.room_type_id=r.room_type_id', 'left');
		$this->db->where('rr.resv_room_date', $date);
		$this->db->where('rr.deleted', 'no');
		$this->db->where('rr.resv_id !=','NULL');
		$this->db->order_by('r.room_number', 'asc');
		
		return $this->db->get();
	}
	
	function select_monthly_occupied($month, $year) {
		$this->db->select('rr.resv_room_date, r.room_number, rt.room_type_name');
		$this->db->from('reservation_room as rr');
		$this->db->join('room as r', 'r.room_id=rr.room_id', 'left');
		$this->db->join('room_type as rt', 'rt.room_type_id=r.room_type_id', 'left');
		$this->db->where('MONTH(rr.resv_room_date)', $month);
		$this->db->where('YEAR(rr.resv_room_date)', $year);
		$this->db->where('rr.deleted', 'no');
		$this->db->where('rr.resv_id !=','NULL');
		$this->db->order_by('rr.resv_room_date', 'asc');
		
		return $this->db->get();
	}
	
	function select_daily_vacant($date) {
		$occ = $this->select_daily_occupied($date)->result_array();
		$occ_room = array();
		foreach($occ as $room) {
			$occ_room[] = $room['room_id'];
		}
		
		$this->db->select('r.room_id, r.room_number, rt.room_type_name');
		$this->db->from('room as r');
		$this->db->join('room_type as rt', 'rt.room_type_id=r.room_type_id', 'left');
		if(count($occ_room) > 0)$this->db->where_not_in('r.room_id', $occ_room);
		$this->db->order_by('r.room_number', 'asc');
		
		return $this->db->get();
	}
	
	function select_daily_reservation($date) {
		$this->db->select('rr.check_in_time, rr.check_out_time, ro.room_number, rt.room_type_name, g.guest_name, g.nationality, (r.resv_adult_number+r.resv_child_number+r.resv_infant_number) as pax, rr.resv_status');
		$this->db->from('reservation_room as rr');
		$this->db->join('room as ro', 'ro.room_id=rr.room_id', 'left');
		$this->db->join('room_type as rt', 'rt.room_type_id=ro.room_type_id', 'left');
		$this->db->join('reservation as r', 'r.resv_id=rr.resv_id', 'left');
		$this->db->join('guest as g', 'g.guest_id=r.guest_id', 'left');
		$this->db->where('rr.resv_id !=', 'NULL');
		$this->db->where('rr.deleted', 'no');
		$this->db->where('rr.resv_room_date', $date);
		$this->db->order_by('rr.check_in_time', 'asc');
		
		return $this->db->get();
	}
	
	function select_monthly_reservation($month, $year) {
		$this->db->select('rr.resv_room_date, rr.check_in_time, rr.check_out_time, ro.room_number, rt.room_type_name, g.guest_name, g.nationality, (r.resv_adult_number+r.resv_child_number+r.resv_infant_number) as pax, rr.resv_status');
		$this->db->from('reservation_room as rr');
		$this->db->join('room as ro', 'ro.room_id=rr.room_id', 'left');
		$this->db->join('room_type as rt', 'rt.room_type_id=ro.room_type_id', 'left');
		$this->db->join('reservation as r', 'r.resv_id=rr.resv_id', 'left');
		$this->db->join('guest as g', 'g.guest_id=r.guest_id', 'left');
		$this->db->where('rr.resv_id !=', 'NULL');
		$this->db->where('rr.deleted', 'no');
		$this->db->where('MONTH(rr.resv_room_date)', $month);
		$this->db->where('YEAR(rr.resv_room_date)', $year);
		$this->db->order_by('rr.resv_room_date', 'asc');
		
		return $this->db->get();
	}
	
	function select_daily_room($date) {
		$this->db->select();
		$this->db->from('reservation_room as rr');
		$this->db->join('reservation as r', 'r.resv_id=rr.resv_id', 'left');
		$this->db->join('guest as g', 'g.guest_id=r.guest_id', 'left');
		$this->db->join('reservation_payment as rp', 'rp.resv_id=r.resv_id', 'left');
		$this->db->join('payment_type as pt', 'pt.payment_id=rp.payment_id', 'left');
		$this->db->join('room as ro', 'ro.room_id=rr.room_id', 'left');
		$this->db->join('room_type as rt', 'rt.room_type_id=ro.room_type_id', 'left');
		$this->db->where('rr.deleted', 'no');
		$this->db->where('rr.resv_room_date',$date);
		
		return $this->db->get();
	}
	
	function select_monthly_room($month, $year) {
		$this->db->select();
		$this->db->from('reservation_room as rr');
		$this->db->join('reservation as r', 'r.resv_id=rr.resv_id', 'left');
		$this->db->join('guest as g', 'g.guest_id=r.guest_id', 'left');
		$this->db->join('reservation_payment as rp', 'rp.resv_id=r.resv_id', 'left');
		$this->db->join('payment_type as pt', 'pt.payment_id=rp.payment_id', 'left');
		$this->db->join('room as ro', 'ro.room_id=rr.room_id', 'left');
		$this->db->join('room_type as rt', 'rt.room_type_id=ro.room_type_id', 'left');
		$this->db->where('rr.deleted', 'no');
		$this->db->where('MONTH(rr.resv_room_date)',$month);
		$this->db->where('YEAR(rr.resv_room_date)',$year);
		
		return $this->db->get();
	}
	
	function select_service_room($resv_room_id) {
		$this->db->select('sd.*,so.*,rr.*,s.*');
		$this->db->from('service_detail as sd');
		$this->db->join('service_order as so', 'so.serv_ord_id=sd.serv_ord_id', 'left');
		$this->db->join('reservation as r', 'r.resv_id=so.resv_id', 'left');
		$this->db->join('reservation_room as rr','rr.resv_id=r.resv_id', 'left');
		$this->db->join('service as s', 's.service_id=sd.service_id', 'left');
		$this->db->where('so.deleted', 'no');
		$this->db->where('rr.resv_room_id', $resv_room_id);
		
		return $this->db->get();
	}
	
	function select_material_room($resv_room_id) {
		$this->db->select();
		$this->db->from('reservation_material as rm');
		$this->db->join('material as m', 'm.material_id=rm.material_id', 'left');
		$this->db->where('rm.deleted', 'no');
		$this->db->where('rm.resv_room_id', $resv_room_id);
		
		return $this->db->get();
	}
	
}
?>